layout: default title: Tasks nav_order: 1
[toc]
Getting familiar with basic objects in Excel
Open new workbook
Choose File -> New in the Excel menu or press Ctrl+N (Cmd+N on Mac)
image-20210926205209032
Create new worksheet.
In the opened workbook click on a plus in the bottom panel on the left
image-20210926205404343
Read more about difference between Workbook and Worksheet objects [SOURCE]
Switching between data types
Most of the data is defined as a General type, unless specified otherwise. To learn what type a specific column is, select the range of values and check the dropdown list value in the Home tab.
Hint: the default alignment used by Excel suggests the data type too: numbers are aligned to the right, while text is aligned to the left.
What type is Group column?
What type is District Code column?
Change District Code column to be Text.
Select values in District Code column (or select all the values in the column by clicking on the A in the column names pane) and then select Text instead of General
image-20210926211001795
How to avoid having numbers stored as dates
Try to put 1-20 in the first row of the Group-District Code column
Select filled cell (D5) and change type to be Text. Did it work?
Unfortunately, Excel does not allow to switch between dates and text after the transformation has been done. However, we may prevent the automatic switch to the date format by choosing the format of column.
Delete the value in the cell. Change the Group-District Code column to be Text. Now. fill the first 5 values manually following the pattern (1-20, 2-20,3-20, 4-20,TOTAL-20 ).
How to fill values based on the pattern
We now can fill the rest of the patern using Autofill option.
Hint: you can also use CONCAT formula to fill the pattern using values from Group and District Name columns. Please note, that in this case you need to change column type to be General for the formula to work.
Select first 5 rows of Group-District Code column and drag the fill handle down
image-20210926212807828
Explore existing sorting options
Sort data by the total number of students (ascending order).
Select the Number of Students column header (cell E4) and click Sort A to Z in the Data menu tab.
Hint: Applying Filter allows to use different sorting options as well
image-20210926213258804
Add another level of sorting, District Code.
Follow the previous step now for District Code column, or use Sort option in the Data menu to add several rules and change their hierarchies
image-20210926213913533
Removing errors using Filter
Apply Filter (located in the Data menu) to the District Code column;
image-20210926214341684
Select rows with blank District Code;
image-20210926214441951
Right click on selected rows and select option “Delete Rows”;
image-20210926214539172
Make sure you do not have any blanks left
image-20210926214706616
3 ways to remove empty rows: https://www.ablebits.com/office-addins-blog/2013/10/01/remove-blank-rows-in-excel/
Working with missing or masked values
Use Replace to find all “Msk” entries
In the Find&Select option of main menu select Replace option or click Ctrl+H (both for PC and Mac);
image-20210926214917173
Replace “Msk” with blank.
Do not put anything in the Replace field, not even a space
image-20210926215139163
Use Go-To-Special to highlight blank cells
Select the table area (not including headers) and select Go To Special from the Find&Select options. Click Fill Color to highlight selected cells
image-20210926215537641
image-20210926215656596
Filling empty cells using Go To Special: https://spreadsheetplanet.com/fill-blank-cells-with-value-above-in-excel/